﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using Models;
using Utility;
using Service.Interface;
using Repository.Interface;
using SqlSugar;

namespace Service.Impl
{
    public class ShipmentServices : IShipmentServices
    {
        private IBaseRepository _PackinforRepository;
        private SqlSugarClient _db;
        public ShipmentServices(ISqlSugarClient db, IBaseRepository PackinforRepository)
        {
            _db = db as SqlSugarClient;
            _PackinforRepository = PackinforRepository;
        }

        public bool InsertProductShipment(List<ProductShipment> List)
        {
            int Count = 0;
            for (int i = 0; i < List.Count; i++)
            {
                List<OracleParameter> parms = new List<OracleParameter>()
                {
                    new OracleParameter(":company",OracleType.VarChar){ Value=List[i].Company},
                    new OracleParameter(":packnum",OracleType.Int32){ Value=List[i].PackNum},
                    new OracleParameter(":partnum",OracleType.VarChar){ Value=List[i].PartNum},
                    new OracleParameter(":shipqty",OracleType.Double){ Value=List[i].ShipQty},
                    new OracleParameter(":lotnum",OracleType.VarChar){ Value=List[i].LotNum},
                    new OracleParameter(":shipdate",OracleType.DateTime){ Value=List[i].ShipDate}
                };

                string Insertsql = $@"
                    INSERT INTO PRODUCT_SHIPMENT
                    (COMPANY, PACKNUM, PARTNUM, SHIPQTY, LOTNUM, SHIPDATE)
                    VALUES (:company, :packnum, :partnum, :shipqty, :lotnum, :shipdate)";
                Count += _db.GetConnection("OUSB").Ado.ExecuteCommand(Insertsql.Replace(@"[\r\n]", ""), parms.ToArray());
            }
            return List.Count == Count ? true : false;
        }

        public bool SynProductShipment(List<ProductShipment> List)
        {
            int Count = 0;
            for (int i = 0; i < List.Count; i++)
            {
                List<OracleParameter> parms = new List<OracleParameter>()
                {
                    new OracleParameter(":company",OracleType.VarChar){ Value=List[i].Company},
                    new OracleParameter(":packnum",OracleType.Int32){ Value=List[i].PackNum},
                    new OracleParameter(":partnum",OracleType.VarChar){ Value=List[i].PartNum},
                    new OracleParameter(":shipqty",OracleType.Double){ Value=List[i].ShipQty},
                    new OracleParameter(":shipdate",OracleType.DateTime){ Value=DateTime.Now}
                };
                string MergeInto = @"
			    MERGE INTO PRODUCT_SHIPMENT tgt
			    USING DUAL ON (tgt.COMPANY=:company AND tgt.PACKNUM=:packnum AND tgt.PartNum=:partnum)
                WHEN MATCHED THEN
                UPDATE
                SET
	                tgt.SHIPQTY = :shipqty,
                    tgt.SHIPDATE = :shipdate
                WHEN NOT MATCHED THEN
                INSERT
	                (COMPANY,PACKNUM,PARTNUM,SHIPQTY,SHIPDATE)
                VALUES (:company,:packnum,:partnum,:shipqty,:shipdate)";
                Count += _db.GetConnection("OUSB").Ado.ExecuteCommand(MergeInto.Replace(@"[\r\n]", ""), parms.ToArray());
            }
            return List.Count == Count ? true : false;
        }

        public IList<ProductShipment> GetProductShipments()
        {
            string strsql = $@"
            --STATUS = 0 false ; 1 true; 
            --MEMBERID,COMPANY,PACKNUM,PARTNUM,SHIPQTY,LOTNUM,SHIPDATE, 
            SELECT 
                MEMBERID,COMPANY,PACKNUM,
                CASE WHEN STATUS = 0 THEN 'true'   
		                 WHEN STATUS = 1 THEN 'false' 
                END AS READYTOINVOICE,
                SHIPDATE
            FROM PRODUCT_SHIPMENT 
            WHERE STATUS = 0
                AND SHIPDATE >= to_date('{DateTime.Now.AddDays(-7).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')
                AND SHIPDATE <= to_date('{DateTime.Now.AddDays(1).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')";
            var dat = _db.GetConnection("OUSB").Ado.GetDataTable(strsql.Replace(@"[\r\n]", ""));
            return GeneralMethod.DataToList<ProductShipment>(dat);
        }

        public bool UpadteProductShipments(List<int> id)
        {
            string strsql = $@"
            UPDATE PRODUCT_SHIPMENT 
                   SET SHIPDATE = to_date('{DateTime.Now.ToString("yyyy-MM-dd")}', 'yyyy-mm-dd') ,
				   STATUS= 1 
            WHERE MEMBERID IN({string.Join("','", id)})
                  AND SHIPDATE >= to_date('{DateTime.Now.AddDays(-1).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')
                  AND SHIPDATE <= to_date('{DateTime.Now.AddDays(1).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')";
            return id.Count == _db.GetConnection("OUSB").Ado.ExecuteCommand(strsql.Replace(@"[\r\n]", "")) ? true : false;
        }

        public IList<LaborDetail> GetLaborDetail(string jobnum,string company)
        {
            string strsql = $@"SELECT * FROM  LABOR_DETAIL WHERE JOBNUM='{jobnum}' AND COMPANY='{company}'";
            var dat = _db.GetConnection("OUSB").Ado.GetDataTable(strsql.Replace(@"[\r\n]", ""));
            return GeneralMethod.DataToList<LaborDetail>(dat);
        }

    }
}
